﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using System.Collections.Specialized;

namespace GeneratorLibrary.DatabaseCompare {
    class ColumnDictionary : KeyedCollection<string, Column> {
        protected override string GetKeyForItem(Column item) {
            return item.Name;
        }
    }
    class AlterTable {


        public List<string> doAlterTable(Table current, Table target, Server targetServer) {
            Microsoft.SqlServer.Management.Common.SqlExecutionModes oldMode = targetServer.ConnectionContext.SqlExecutionModes;
            targetServer.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql;

            ScriptingOptions so = new ScriptingOptions();

            ColumnDictionary targetColumns = new ColumnDictionary();
            ColumnDictionary newCurrent = new ColumnDictionary();
            List<Index> targetIndexes = new List<Index>();
            List<ForeignKey> targetFKs = new List<ForeignKey>();

            List<string> alterColumnStatements = new List<string>();
            List<string> dropIndexStatements = new List<string>();
            List<string> dropColumnStatements = new List<string>();
            List<string> createIndexStatements = new List<string>();
            List<string> createColumnStatements = new List<string>();

            List<string> dropFKStatements = new List<string>();
            List<string> createFKStatements = new List<string>();
            
            foreach (Column c in target.Columns) {
                targetColumns.Add(c);
            }
            foreach (Index indexTarget in target.Indexes) {
                targetIndexes.Add(indexTarget);
            }
            foreach (ForeignKey fk in target.ForeignKeys) {
                targetFKs.Add(fk);
            }
            foreach (Column c in current.Columns) {
                if (targetColumns.Contains(c.Name)) {
                    Column targetCol = targetColumns[c.Name];
                    targetColumns.Remove(c.Name);
                    string[] alter;
                    if (!HasSameProperties(c, targetCol, target, targetServer, out alter)) {
                        alterColumnStatements.AddRange(alter);
                    }
                } else {
                    newCurrent.Add(c);
                }
            }

            foreach (Index index in current.Indexes) {
                bool isFound = false;
                foreach (Index indexTarget in targetIndexes) {
                    if (indexEquals(index, indexTarget)) {
                        isFound = true;
                        targetIndexes.Remove(indexTarget);
                        break;
                    }
                }
                if (!isFound) {
                    target.Indexes.Add(index);
                    foreach (string s in targetServer.ConnectionContext.CapturedSql.Text) {
                        createIndexStatements.Add(s);
                    }
                    targetServer.ConnectionContext.CapturedSql.Clear();
                }
            }

            foreach (Index indexTarget in targetIndexes) {
                indexTarget.Drop();
                foreach (string s in targetServer.ConnectionContext.CapturedSql.Text) {
                    dropIndexStatements.Add(s);
                }
                targetServer.ConnectionContext.CapturedSql.Clear();
            }
            
            foreach (ForeignKey fk in current.ForeignKeys) {
                bool isFound = false;
                foreach (ForeignKey fkTarget in targetFKs) {
                    if (fkEquals(fk, fkTarget)) {
                        isFound = true;
                        targetFKs.Remove(fkTarget);
                        break;
                    }
                }
                if (!isFound) {
                    target.ForeignKeys.Add(fk);
                    foreach (string s in targetServer.ConnectionContext.CapturedSql.Text) {
                        createFKStatements.Add(s);
                    }
                    targetServer.ConnectionContext.CapturedSql.Clear();
                }
            }

            foreach (ForeignKey fkTarget in targetFKs) {
                fkTarget.Drop();
                foreach (string s in targetServer.ConnectionContext.CapturedSql.Text) {
                    dropFKStatements.Add(s);
                }
                targetServer.ConnectionContext.CapturedSql.Clear();
            }

            //confirm with user if any should be matching (lists must both have elements)
            //  LEFT>non-matching column list
            //  vs
            //  RIGHT>List
            //  foreach confirmation
            //    --> script alter

            foreach (Column c in newCurrent) {
                target.Columns.Add(c);
                target.Alter();
                foreach (string s in targetServer.ConnectionContext.CapturedSql.Text) {
                    createColumnStatements.Add(s);
                }
                targetServer.ConnectionContext.CapturedSql.Clear();
            }

            foreach (Column c in targetColumns) {
                c.Drop();
                foreach (string s in targetServer.ConnectionContext.CapturedSql.Text) {
                    dropColumnStatements.Add(s);
                }
                targetServer.ConnectionContext.CapturedSql.Clear();
            }

            List<string> output = new List<string>();
            output.AddRange(dropIndexStatements);
            output.AddRange(dropFKStatements);
            output.AddRange(dropColumnStatements);
            output.AddRange(createColumnStatements);
            output.AddRange(alterColumnStatements);
            output.AddRange(createFKStatements);
            output.AddRange(createIndexStatements);

            targetServer.ConnectionContext.CapturedSql.Clear();//just to make sure they are not there.
            targetServer.ConnectionContext.SqlExecutionModes = oldMode;

            return output;
        }

        private bool fkEquals(ForeignKey fk, ForeignKey fkTarget) {
            if (fk.DeleteAction != fkTarget.DeleteAction || fk.UpdateAction != fkTarget.UpdateAction)
                return false;
            if (fk.ReferencedKey != fkTarget.ReferencedKey || fk.ReferencedTable != fkTarget.ReferencedTable)
                return false;
            if (fk.Columns.Count != fkTarget.Columns.Count)
                return false;
            for (int i = 0; i < fk.Columns.Count; i++) {
                if (fk.Columns[i].ReferencedColumn != fkTarget.Columns[i].ReferencedColumn)
                    return false;
            }
            return true;
        }

        private bool HasSameProperties(Column c, Column c2, Table target, Server targetServer, out string[] alter) {
            bool retValue = true, defConstraintAlter = false;
            List<string> scripts = new List<string>();
            if (c.DataType.Name != c2.DataType.Name) {
                retValue = false;
                c2.DataType = c.DataType;
            } else {
                if (c.DataType.MaximumLength != c2.DataType.MaximumLength) {
                    c2.DataType.MaximumLength = c.DataType.MaximumLength;
                    retValue = false;
                }
                if (c.DataType.NumericPrecision != c2.DataType.NumericPrecision) {
                    c2.DataType.NumericPrecision = c.DataType.NumericPrecision;
                    retValue = false;
                }
                if (c.DataType.NumericScale != c2.DataType.NumericScale) {
                    c2.DataType.NumericScale = c.DataType.NumericScale;
                    retValue = false;
                }
                if (c.DataType.SqlDataType != c2.DataType.SqlDataType) {
                    c2.DataType.SqlDataType = c.DataType.SqlDataType;
                    retValue = false;
                }
            }
            if (c.Identity != c2.Identity) {
                retValue = false;
                c2.Identity = c.Identity;
                if (c.Identity) {
                    c2.IdentityIncrement = c.IdentityIncrement;
                    c2.IdentitySeed = c.IdentitySeed;
                }
            }

            if (c.DefaultConstraint != null || c2.DefaultConstraint != null) {
                if (c.DefaultConstraint == null) {
                    retValue = false;
                    c2.DefaultConstraint.Drop();
                } else if (c2.DefaultConstraint == null) {
                    foreach (string s in c.DefaultConstraint.Script()) {
                        scripts.Add(s);
                    }
                } else {
                    if (c.DefaultConstraint.Text.Trim(new char[]{'(',')'}) != c2.DefaultConstraint.Text.Trim(new char[]{'(',')'})) {
                        retValue = false;
                        defConstraintAlter = true;
                        c2.DefaultConstraint.Text = c.DefaultConstraint.Text;
                    }
                }
            } else {
                if (c.Default != c2.Default) {
                    retValue = false;
                    c2.Default = c.Default;
                }
            }
            if (retValue)
                alter = new string[scripts.Count];
            else {
                c2.Alter();
                //if (defConstraintAlter)
                //    c2.DefaultConstraint.Alter();

                alter = new string[targetServer.ConnectionContext.CapturedSql.Text.Count + scripts.Count];
                targetServer.ConnectionContext.CapturedSql.Text.CopyTo(alter, 0);
                targetServer.ConnectionContext.CapturedSql.Clear();                
            }
            if (scripts.Count > 0) {
                retValue = true;
                scripts.CopyTo(alter, alter.Length - scripts.Count);
            }

            return retValue;
        }

        private bool indexEquals(Index index, Index indexTarget) {
            //always same properties... not sure if they're always in the same order.
            try {
                for (int i = 0; i < index.Properties.Count; i++) {
                    string name = index.Properties[i].Name;
                    if (name != "ID") {
                        if (index.Properties[i].Value != indexTarget.Properties[name].Value)
                            return false;
                    }
                }
                if (index.IndexedColumns.Count != indexTarget.IndexedColumns.Count)
                    return false;
                for (int i = 0; i < index.IndexedColumns.Count; i++) {
                    if (index.IndexedColumns[i].Name != indexTarget.IndexedColumns[i].Name)
                        return false;
                    if (index.IndexedColumns[i].Descending != indexTarget.IndexedColumns[i].Descending)
                        return false;
                }
            } catch {
                return false;
            }
            return true;
        }

    }
}
